﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StockFinder.DataAccess;
using StockFinder.Services.Extractors.Yahoo;
using StockFinder.Model;
using System.Data;
using StockFinder.Services.Extractors;
using log4net;
using System.Data.SqlClient;
using StockFinder.GoogleFinance;

namespace StockFinder.Services.Engines
{
    public class DailyPricesHistoryDatabaseUpdater
    {
        private static readonly ILog _Log = LogManager.GetLogger(typeof(DailyPricesHistoryDatabaseUpdater));

        public void Run(List<string> symbols, bool truncate)
        {
            IHistoricPricesExtractor yahooPricesExtractor = new YahooHistoricPriceExtractor();
            IHistoricDailyPricesExtractor googlePricesExtractor = new GoogleFinanceOnlineStockPriceHistoryExtractor(); 
            List<DailyPrice> prices = null;
            const int YEARS_BACK_TO_DOWNLOAD = 30;
            string symbol = null;

            #region Prepare DailyPrice table
            if (truncate)
            {
                _Log.Info("Truncating dbo.DailyPrice");

                //truncate price table
                DailyPriceDataAccess.TruncateDailyPriceTable();
            }           

            _Log.Info("Removing Index");

            DailyPriceDataAccess.RemoveDailyPriceIndex();
            #endregion

            #region Create DailyPrice DataTable shcema
            _Log.Debug("Generating table schema");

            DataTable pricesTable = new DataTable("DailyPrice");
            pricesTable.Columns.Add("Open", typeof(decimal));
            pricesTable.Columns.Add("High", typeof(decimal));
            pricesTable.Columns.Add("Low", typeof(decimal));
            pricesTable.Columns.Add("Close", typeof(decimal));
            pricesTable.Columns.Add("Volume", typeof(long));
            pricesTable.Columns.Add("Symbol", typeof(string));
            pricesTable.Columns.Add("PriceDate", typeof(DateTime));
            pricesTable.Columns.Add("AdjustedClose", typeof(decimal));
            pricesTable.Columns.Add("AdjustedOpen", typeof(decimal));
            pricesTable.Columns.Add("AdjustedHigh", typeof(decimal));
            pricesTable.Columns.Add("AdjustedLow", typeof(decimal));
            #endregion

            SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString);
            connection.Open();

            SqlBulkCopy bulkCopy = new SqlBulkCopy(connection,
                        SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
            bulkCopy.DestinationTableName = "DailyPrice";

            #region Loop symbols and insert prices
            _Log.Debug("Beginning loop of symbols");

            try
            {
                //download price history
                for (int i = 0; i < symbols.Count; i++)
                {
                    symbol = symbols[i];
                    pricesTable.Rows.Clear();

                    #region Get symbol prices
                    _Log.InfoFormat("Downloading prices for {0}", symbol);

                    if (symbol.EndsWith(".L"))
                    {
                        prices = googlePricesExtractor.GetHistoricPrices(
                            new Symbol { Name = symbol.Replace(".L",""), Exchange = Model.Enumerations.Exchange.LONDON }
                            , DateTime.Now.AddYears(-YEARS_BACK_TO_DOWNLOAD), DateTime.Today); 
                    }
                    else
                    {
                        prices = yahooPricesExtractor.GetHistoricPrices(DateTime.Now.AddYears(-YEARS_BACK_TO_DOWNLOAD), DateTime.Today, symbol);
                    }                    

                    //do we need to format symbol name?
                    if (prices == null)
                    {
                        #region Symbol name reformat #1
                        _Log.WarnFormat("Problem getting prices for {0}", symbol);

                        //try ABVC -> ABV-C
                        string formattedSymbol = string.Format("{0}-{1}", symbol.Substring(0, symbol.Length - 1), symbol[symbol.Length - 1]);

                        _Log.DebugFormat("Symbol name format attempt 1: {0}", formattedSymbol);

                        prices = yahooPricesExtractor.GetHistoricPrices(DateTime.Now.AddYears(-YEARS_BACK_TO_DOWNLOAD), DateTime.Today, formattedSymbol);
                        #endregion

                        #region Symbol name reformat #2
                        //did reformat try #1 work?
                        if (prices == null)
                        {
                            _Log.WarnFormat("Problem getting prices for {0}", formattedSymbol);

                            //no, so try ABVC -> ABV-PC
                            formattedSymbol = string.Format("{0}-P{1}", symbol.Substring(0, symbol.Length - 1), symbol[symbol.Length - 1]);

                            _Log.DebugFormat("Symbol name format attempt 2: {0}", formattedSymbol);

                            prices = yahooPricesExtractor.GetHistoricPrices(DateTime.Now.AddYears(-YEARS_BACK_TO_DOWNLOAD), DateTime.Today, formattedSymbol);
                        }
                        #endregion
                    }
                    #endregion

                    #region Add prices to database
                    //make sure we have some prices
                    if (prices != null)
                    {
                        #region Prepare and fill Datatable for entry
                        _Log.DebugFormat("Adding {0} prices", prices.Count);

                        _Log.Debug("Converting prices into DataRows");

                        foreach (var price in prices)
                        {
                            var row = pricesTable.NewRow();
                            row["Open"] = price.Open;
                            row["High"] = price.High;
                            row["Low"] = price.Low;
                            row["Close"] = price.Close;
                            row["Volume"] = price.Volume;
                            row["Symbol"] = symbol;
                            row["PriceDate"] = price.PriceDate;
                            row["AdjustedClose"] = price.AdjustedClose;
                            row["AdjustedOpen"] = price.AdjustedOpen;
                            row["AdjustedHigh"] = price.AdjustedHigh;
                            row["AdjustedLow"] = price.AdjustedLow;
                            pricesTable.Rows.Add(row);
                        }
                        #endregion

                        #region Write to SQL Server
                        _Log.Debug("Calling bulkCopy.WriteToServer");

                        for (int tries = 0; tries < 5; tries++)
                        {
                            try
                            {
                                bulkCopy.WriteToServer(pricesTable);
                                break;
                            }
                            catch (Exception exception)
                            {
                                _Log.ErrorFormat("Try {0} failed. An unexpected error occurred bulk copying to database -> {1}",
                                    (tries + 1), exception);
                            }
                        }
                        #endregion

                        _Log.DebugFormat("Added prices");
                    }
                    else
                    {
                        _Log.WarnFormat("Could not get prices for {0}", symbol);
                    }

                    _Log.InfoFormat("{0:0.0%} completed", (double)i / symbols.Count);
                    #endregion
                }
            }
            catch (Exception exception)
            {
                _Log.ErrorFormat("An unexpected error occurred -> {0}", exception);
            }
            finally
            {
                connection.Dispose();
            }

            #endregion

            #region DailyPrice table clean up
            _Log.InfoFormat("Rebuilding Index");

            DailyPriceDataAccess.CreateDailyPriceIndex();
            #endregion

            _Log.InfoFormat("Done");
        }

        public void Run(List<string> symbols)
        {
            Run(symbols, true);
        }
    }
}
